import MySQLdb
from .OnlineHandler import OnlineHandler
from dvadmin.utils.sqlprocess import  dict_int_change
from jinjasql import JinjaSql
from ..models import Datasource

class mysqlSource:
    def __init__(self):
        self.conn_list = {}

    def connect(self,name, host, port, user, password, database):
        conn  = MySQLdb.connect(
            host=host,
            port=port,
            user=user,
            passwd=password,
            db=database
        )
        self.conn_list[name] = conn
    def testconnect(self,host, port, user, password, database):
        try:
            conn  = MySQLdb.connect(
                host=host,
                port=port,
                user=user,
                passwd=password,
                db=database
            )
            return True,None
        except Exception as e:
            return False,e

    def cursor(self,name):
        if name in self.conn_list.keys():
            return self.conn_list[name].cursor()
        return

mysqlhandel = mysqlSource()

class MysqlHandel(OnlineHandler):
    def __init__(self,obj,requestdata, id,test):
        self.obj = obj
        self.requestdata = requestdata
        self.userid = id
        self.query = None
        self.bind_params =None
        self.newQuery = None
        self.curor = None
        self.test =test
        self.j = JinjaSql()

    def sqlprocess(self):
        self.newQuery = dict_int_change(self.obj['parameter'], self.requestdata, self.userid)
        self.query, self.bind_params = self.j.prepare_query(self.obj['sql'], self.newQuery)
    def createCursor(self):
        conn = mysqlhandel.cursor(self.obj['datasource'])
        if conn:
            self.cursor = conn
            return
        obj = Datasource.objects.get(id= self.obj['datasource'])

        mysqlhandel.connect(obj.id,obj.host,obj.port,obj.user,obj.password,obj.database)
        self.cursor = mysqlhandel.cursor(self.obj['datasource'])


    def query_all_dict(self,sql, params=None,nopagesql=None):
        rowList = []
        num = 0
        if params:
            num = self.cursor.execute(sql, params)
        else:
            mum = self.cursor.execute(sql)
        col_names = [desc[0] for desc in self.cursor.description]
        row = self.cursor.fetchall()
        for list in row:
            tMap = dict(zip(col_names, list))
            rowList.append(tMap)
        if nopagesql:
            if params:
                num = self.cursor.execute(nopagesql,params)
            else:
                num = self.cursor.execute(nopagesql)
        self.cursor.close()
        return rowList, col_names, num

    def process(self):
        if self.obj['datatyped'] == 2:
            page = 1
            pagesize = 10
            if self.newQuery.get("page"):
                page = int(self.newQuery["page"])
            if self.newQuery.get("limit"):
                pagesize = int(self.newQuery["limit"])
            pages = (page - 1) * pagesize
            newquery = self.query + " limit " + str(pages) + "," + str(pagesize)
            data, cols_name, num = self.query_all_dict(newquery, self.bind_params, self.query)
            return {"data": data, 'total': num, 'cols_name': cols_name}

        data, cols_name, num = self.query_all_dict( self.query,  self.bind_params)
        if self.obj['datatyped'] == 0:
            if self.test:
                return {"data": data,'cols_name':cols_name}
            return data[0]
        elif self.obj['datatyped']  == 1:
            return {"data": data,'cols_name':cols_name}
